﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using Wuqi.Webdiyer;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace AspNetCs.Common
{
    public class DataPagesOperate
    {

        /// <summary>
        /// 绑定Repeater控件
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="aspNetPager">分页控件</param>
        /// <param name="orderProperty">排序字段(如果没有排序，该参数的值为null或空字符串)</param>
        /// <param name="countProperty">查询记录的条数通过该字段(如果该参数的值为null或空字符串，则通过所有字段查询记录条数)</param>
        /// <param name="rpt">Repteater控件</param>
        public void BindRepeater(string sql, AspNetPager aspNetPager, string orderProperty, string countProperty, Repeater rpt)
        {
            sql = sql.ToLower();
            orderProperty = orderProperty.ToLower();
            countProperty = countProperty.ToLower();
            //string sql = "select * from gw_newsletter_group";//自定义的SQL语句
            int recordcount;

            DataSet ds = GetPage(sql.ToLower(), aspNetPager.CurrentPageIndex, aspNetPager.PageSize, out recordcount, orderProperty, countProperty);
            aspNetPager.RecordCount = recordcount;
            rpt.DataSource = ds;
            rpt.DataBind();
            aspNetPager.CustomInfoHTML = "记录总数：<b>" + aspNetPager.RecordCount.ToString() + "</b>";
            aspNetPager.CustomInfoHTML += " 总页数：<b>" + aspNetPager.PageCount.ToString() + "</b>";
            aspNetPager.CustomInfoHTML += " 当前页：<font color=\"red\"><b>" + aspNetPager.CurrentPageIndex.ToString() + "</b></font>";
        }


        /// <summary>
        /// 绑定Repeater控件
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="aspNetPager">分页控件</param>
        /// <param name="orderProperty">排序字段(如果没有排序，该参数的值为null或空字符串)</param>
        /// <param name="countProperty">查询记录的条数通过该字段(如果该参数的值为null或空字符串，则通过所有字段查询记录条数)</param>
        /// <param name="recordcount">总条数</param>
        /// <param name="rpt">Repteater控件</param>
        public void BindRepeater(string sql, AspNetPager aspNetPager, string orderProperty, string countProperty, Repeater rpt, out int recordcount)
        {
            //string sql = "select * from gw_newsletter_group";//自定义的SQL语句


            DataSet ds = GetPage(sql, aspNetPager.CurrentPageIndex, aspNetPager.PageSize, out recordcount, orderProperty, countProperty);
            aspNetPager.RecordCount = recordcount;
            rpt.DataSource = ds;
            rpt.DataBind();
            aspNetPager.CustomInfoHTML = "记录总数：<b>" + aspNetPager.RecordCount.ToString() + "</b>";
            aspNetPager.CustomInfoHTML += " 总页数：<b>" + aspNetPager.PageCount.ToString() + "</b>";
            aspNetPager.CustomInfoHTML += " 当前页：<font color=\"red\"><b>" + aspNetPager.CurrentPageIndex.ToString() + "</b></font>";
        }

        /// <summary>
        /// 绑定GridView控件
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="aspNetPager">分页控件</param>
        /// <param name="orderProperty">排序字段(如果没有排序，该参数的值为null或空字符串)</param>
        /// <param name="countProperty">查询记录的条数通过该字段(如果该参数的值为null或空字符串，则通过所有字段查询记录条数)</param>
        /// <param name="rpt">GridView控件</param>
        public void BindGridView(string sql, AspNetPager aspNetPager, string orderProperty, string countProperty, Repeater gridView)
        {
            //string sql = "select * from gw_newsletter_group";//自定义的SQL语句
            int recordcount;

            DataSet ds = GetPage(sql, aspNetPager.CurrentPageIndex, aspNetPager.PageSize, out recordcount, orderProperty, countProperty);
            aspNetPager.RecordCount = recordcount;
            gridView.DataSource = ds;
            gridView.DataBind();
            aspNetPager.CustomInfoHTML = "记录总数：<b>" + aspNetPager.RecordCount.ToString() + "</b>";
            aspNetPager.CustomInfoHTML += " 总页数：<b>" + aspNetPager.PageCount.ToString() + "</b>";
            aspNetPager.CustomInfoHTML += " 当前页：<font color=\"red\"><b>" + aspNetPager.CurrentPageIndex.ToString() + "</b></font>";
        }


        /// <summary>
        /// 绑定GridView控件
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="aspNetPager">分页控件</param>
        /// <param name="orderProperty">排序字段(如果没有排序，该参数的值为null或空字符串)</param>
        /// <param name="countProperty">查询记录的条数通过该字段(如果该参数的值为null或空字符串，则通过所有字段查询记录条数)</param>
        /// <param name="recordcount">总条数</param>
        /// <param name="rpt">GridView控件</param>
        public void BindGridView(string sql, AspNetPager aspNetPager, string orderProperty, string countProperty, Repeater gridView, out int recordcount)
        {
            //string sql = "select * from gw_newsletter_group";//自定义的SQL语句


            DataSet ds = GetPage(sql, aspNetPager.CurrentPageIndex, aspNetPager.PageSize, out recordcount, orderProperty, countProperty);
            aspNetPager.RecordCount = recordcount;
            gridView.DataSource = ds;
            gridView.DataBind();
            aspNetPager.CustomInfoHTML = "记录总数：<b>" + aspNetPager.RecordCount.ToString() + "</b>";
            aspNetPager.CustomInfoHTML += " 总页数：<b>" + aspNetPager.PageCount.ToString() + "</b>";
            aspNetPager.CustomInfoHTML += " 当前页：<font color=\"red\"><b>" + aspNetPager.CurrentPageIndex.ToString() + "</b></font>";
        }

        /// <summary>
        /// 获得某一页的数据
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="currentPage">当前的页数</param>
        /// <param name="pagesize">每页记录的条数</param>
        /// <param name="recordcount">记录的总条数</param>
        /// <param name="orderProperty">排序的字段</param>
        /// <param name="countProperty">查询总记录数据检索的字段</param>
        /// <returns></returns>
        private DataSet GetPage(string sql, int currentPage, int pagesize, out int recordcount, string orderProperty, string countProperty)
        {
            SqlDataAdapter ada = new SqlDataAdapter(sql, GetConnection());
            DataSet ds = new DataSet();
            int startRow = (currentPage - 1) * pagesize;
            ada.Fill(ds, startRow, pagesize, "table");
            recordcount = GetPageRecord(sql, orderProperty, countProperty);
            return ds;
        }

        private int GetPageRecord(string sql, string orderProperty, string countProperty)
        {
            if (!string.IsNullOrEmpty(orderProperty))
            {
                sql = System.Text.RegularExpressions.Regex.Replace(sql, "order by " + orderProperty, "");
            }
            if (string.IsNullOrEmpty(countProperty))
            {
                sql = "select count(*) from (" + sql + ") as temp";
            }
            else
            {
                sql = "select count(" + countProperty + ") from (" + sql + ") as temp";
            }

            SqlCommand cmd = new SqlCommand(sql, GetConnection());
            cmd.Connection.Open();
            int recordcount = (int)cmd.ExecuteScalar();
            return recordcount;
        }
        private int GetPagerCount(string sql, int pagesize, string orderProperty, string countProperty)
        {
            int recordcount = GetPageRecord(sql, orderProperty, countProperty);//方法见上
            int pagecount = recordcount / pagesize;
            return (recordcount % pagesize == 0 ? pagecount : pagecount + 1);
        }

        private SqlConnection GetConnection()
        {
            SqlConnection sqlCon = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["SqlConnectionString"]);
            return sqlCon;
        }
    }
}
